<?php

/**
 * 行为分析-页面分析
 *
 * @author meng.deng
 */

require_once 'CommonController.php';

class Admin_ActDayDataPagevisitController extends Admin_CommonController
{
    /**
     * 初始化函数
     * 
     * @return void
     */
    public function init()
    {
        parent::init();
        $this->_model = new Application_Model_ActDayDataPagevisit();
        $this->_productCode = Application_Model_UserLogin::getInstance()->getUserCurrentProductCode();
        
        $conversionModel = new Application_Model_ActDayDataConversion();
        $this->_whereByClear2 = $conversionModel -> getTestSourceIds(); 
        
        $this->view->title = "管理中心";
    }

    /**
     * 默认动作
     * 
     * @return void
     */
    public function indexAction()
    {

        
        //设置URL模板以及条件
        $urls = array();
        $vars = array();
        $wheres = array();

        
        $vars['osName'] = trim($this->_request->get('osName'));
        $vars['source'] = trim($this->_request->get('source'));
        $vars['sourceId'] = trim($this->_request->get('sourceId'));
        
        $vars['dateType']  = trim($this->_request->get('dateType'))!='' ? trim($this->_request->get('dateType')) : 1;
        $vars['doType'] = 1;//trim($this->_request->get('doType'))!='' ? trim($this->_request->get('doType')) : 1;
        $vars['startTime'] = trim($this->_request->get('startTime'));
        $vars['endTime'] = trim($this->_request->get('endTime'));
        
        if ($vars['dateType']==1){
            $vars['startTime'] = date('Y-m-d',strtotime("-1 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==2){
            $vars['startTime'] = date('Y-m-d',strtotime("-8 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==3){
            $vars['startTime'] = date('Y-m-d',strtotime("-31 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==4){
            $vars['startTime'] = date('Y-m-d',strtotime("-3 months"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==5){
            $vars['startTime'] = '';
            $vars['endTime']   = '';
        }
        
        $urls = $vars;

        $wheres[] = "`productCode` = '{$this->_productCode}'";
        
        if ($vars['startTime'] != '') {
            $wheres[] = "`date` >= '{$vars['startTime']}' ";
        }
        if ($vars['endTime'] != '') {
            $wheres[] = "`date` <= '{$vars['endTime']}' ";
        }
        if ($vars['osName'] != '') {
            $wheres[] = "`osName` = '{$vars['osName']}' ";
        }
        if ($vars['source'] != '') {
            $wheres[] = "`sourceId` IN (SELECT  ms.`sourceId` FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON  ms.memberCode=mm.code   WHERE   mm.name='{$vars['source']}' AND ms.productCode='{$this->_productCode}') ";
        }
        if ($vars['sourceId'] != '') {
            $wheres[] = "`sourceId` = '{$vars['sourceId']}' ";
        }
        
        $wheres[] ="`date`>='2011-10-13'";
        $orderBy = "`pv` DESC";

        //清理测试数据
        $wheres[] = $this->_whereByClear2;

        //获取总记录
        $where = count($wheres) > 0 ? ' WHERE ' . implode(' AND ', $wheres) . ' ' : '';
        $sql = "SELECT pageId,pageName,sum(pv) as pv,sum(uv) as uv FROM `{$this->_model->getTableName()}` {$where} AND pageId NOT IN ('1010','1011','1013') GROUP BY pageId,pageName ORDER BY {$orderBy}";
        $rows = $this ->_model ->getAdapter()->fetchAll($sql);
        foreach($rows as $key => $row) {
            if($row['pageId']=='1009'){
                $rows[$key]['pageName'] = '搜索页';
            }
        }
        
        $this->view->rows = $rows;
        $this->view->vars = $vars;

        //点击事件
        $pageArray = array('1003','1007');
        $pageIdStr = count($pageArray) > 0 ? '  AND `pageId` in ( ' . implode(' , ', $pageArray) . ' )' : '';
        $conversionMode = new Application_Model_ActDayDataConversion();
        $sql = "SELECT pageId,sum(pv) as pv,sum(uv) as uv FROM `{$conversionMode->getTableName()}`  {$where} {$pageIdStr} GROUP BY pageId ORDER BY {$orderBy}";
        $conversionRows = $conversionMode ->getAdapter()->fetchAll($sql);
        $eventArray = array();
        foreach ($conversionRows as $key => $row){
            $eventArray[$key]['pageId'] = $row['pageId'];
            if ($row['pageId']=='1003'){
                $eventArray[$key]['pageName'] = '加入购物车';
            } elseif ($row['pageId']=='1007') {
                $eventArray[$key]['pageName'] = '提交订单';
            }
            $eventArray[$key]['pv'] = $row['pv'];
            $eventArray[$key]['uv'] = $row['uv'];
        }
        $this->view->eventRows = $eventArray;
        
        
        //操作系统
        $osNameRows = $this->_model ->getOsNameArray();
        $this->view->osNameRows = $osNameRows;
        
        
        //列出统计页
        $actPage = new Application_Model_ActPage();
        $pageRows = $this ->_model ->getAdapter()->fetchAll("SELECT * FROM `{$actPage->getTableName()}` WHERE `flag`=1 ORDER BY `sort`");
        $this->view->pageRows = $pageRows;
        
        //渠道列表
        $memberRows = $this->_model->getMemberArray($this->_productCode,$vars);
        $this->view->memberRows = $memberRows;
        
        
    }
    
    /**
     * 获取sourceId
     * @return json string
     */
    public function sourceidsAction() 
    {
        $source = trim($this->_request->getPost('source'));
        $sourceModel = new Application_Model_Source();
        $productCode = Application_Model_UserLogin::getInstance()->getUserCurrentProductCode();
        $rows = $sourceModel ->getAdapter()->fetchAll("SELECT mm.name,ms.`sourceId`  FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON ms.memberCode=mm.code WHERE ms.productCode='{$productCode}' AND ms.`sourceId`!='' AND mm.`name`='{$source}'   Group By mm.name,ms.`sourceId`");
        $res = array();
        foreach($rows as $row) {
            $res[]=$row['sourceId'];
        }
        $json_string=json_encode($res);
        echo $json_string;
        exit();
        
    }
    
    
     /**
     * 访问时长
     * 
     * @return void
     */
    public function visitlengthAction()
    {

        
        //设置URL模板以及条件
        $urls = array();
        $vars = array();
        $wheres = array();

        
        $vars['osName'] = trim($this->_request->get('osName'));
        $vars['source'] = trim($this->_request->get('source'));
        $vars['sourceId'] = trim($this->_request->get('sourceId'));
        
        $vars['dateType']  = trim($this->_request->get('dateType'))!='' ? trim($this->_request->get('dateType')) : 1;
        $vars['doType'] = 1;//trim($this->_request->get('doType'))!='' ? trim($this->_request->get('doType')) : 1;
        $vars['startTime'] = trim($this->_request->get('startTime'));
        $vars['endTime'] = trim($this->_request->get('endTime'));
        
        if ($vars['dateType']==1){
            $vars['startTime'] = date('Y-m-d',strtotime("-1 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==2){
            $vars['startTime'] = date('Y-m-d',strtotime("-8 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==3){
            $vars['startTime'] = date('Y-m-d',strtotime("-31 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==4){
            $vars['startTime'] = date('Y-m-d',strtotime("-3 months"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==5){
            $vars['startTime'] = '';
            $vars['endTime']   = '';
        }
        $urls = $vars;

        $wheres[] = "`productCode` = '{$this->_productCode}'";
        
        if ($vars['startTime'] != '') {
            $wheres[] = "`date` >= '{$vars['startTime']}' ";
        }
        if ($vars['endTime'] != '') {
            $wheres[] = "`date` <= '{$vars['endTime']}' ";
        }
        if ($vars['osName'] != '') {
            $wheres[] = "`osName` = '{$vars['osName']}' ";
        }
        if ($vars['source'] != '') {
            $wheres[] = "`sourceId` IN (SELECT  ms.`sourceId` FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON  ms.memberCode=mm.code   WHERE   mm.name='{$vars['source']}' AND ms.productCode='{$this->_productCode}') ";
        }
        if ($vars['sourceId'] != '') {
            $wheres[] = "`sourceId` = '{$vars['sourceId']}' ";
        }
        
        $wheres[] ="`date`>='2011-10-13'";
        $orderBy = "`pv` DESC";
        
        //清理测试数据
        $wheres[] = $this->_whereByClear2;

        //获取总记录
        $where = count($wheres) > 0 ? ' WHERE ' . implode(' AND ', $wheres) . ' ' : '';
        $sql = "SELECT pageId,pageName,sum(uv) as uv,sum(visitLength) as visitLength FROM `{$this->_model->getTableName()}` {$where} AND pageId NOT IN ('1010','1013') GROUP BY pageId,pageName ORDER BY {$orderBy}";
        $rows = $this ->_model ->getAdapter()->fetchAll($sql);
        
        
        //操作系统
        $osNameRows = $this->_model ->getOsNameArray();
        $this->view->osNameRows = $osNameRows;
        
        
        //列出统计页
        $actPage = new Application_Model_ActPage();
        $pageRows = $this ->_model ->getAdapter()->fetchAll("SELECT * FROM `{$actPage->getTableName()}` WHERE `flag`=1 ORDER BY `sort`");
        $reRows = array();
        foreach ($pageRows as $key=>$element){
            foreach($rows as $row) {
                if($element['pageId']==$row['pageId']){
                    $reRows[$key]['pageId'] = $row['pageId'];
                    if($row['pageId']=='1011'){
                        $row['pageName'] = '购物车页面';
                    }
                    if($row['pageId']=='1009'){
                        $row['pageName'] = '搜索页';
                    }
                    $reRows[$key]['pageName'] = $row['pageName'];
                    $reRows[$key]['uv'] = $row['uv'];
                    $reRows[$key]['visitLength'] = $row['visitLength'];
                }
            }
        }
        $this->view->rows = $reRows;
        $this->view->vars = $vars;
        
        //渠道列表
        $memberRows = $this->_model->getMemberArray($this->_productCode,$vars);
        $this->view->memberRows = $memberRows;
        
    }

    /**
     * 获取APP版本号
     * @return json string
     */
    public function appversionAction() 
    {
        $osName = trim($this->_request->getPost('osName'));
        $rows = $this ->_model ->getAdapter()->fetchAll("SELECT `version` FROM `{$this->_model->getTableName()}` WHERE `osNameFlag`='".$osName."' GROUP BY `version` ORDER BY `version`");
        $res = array();
        foreach($rows as $row) {
            $res[]=$row['version'];
        }
        $json_string=json_encode($res);
        echo $json_string;
        exit();
        
    }
    
    /**
     * 专题分析
     * 
     * @return void
     */
    public function topicAction()
    {

        
        //设置URL模板以及条件
        $urls = array();
        $vars = array();
        $wheres = array();

        
        $vars['osName'] = trim($this->_request->get('osName'));
        $vars['source'] = trim($this->_request->get('source'));
        $vars['sourceId'] = trim($this->_request->get('sourceId'));
        
        $vars['dateType']  = trim($this->_request->get('dateType'))!='' ? trim($this->_request->get('dateType')) : 1;
        $vars['doType'] = 1;//trim($this->_request->get('doType'))!='' ? trim($this->_request->get('doType')) : 1;
        $vars['startTime'] = trim($this->_request->get('startTime'));
        $vars['endTime'] = trim($this->_request->get('endTime'));
        $vars['export']   = trim($this->_request->get('export'))!='' ? trim($this->_request->get('export')) :'no';
        $vars['topicName'] = trim($this->_request->get('topicName'));
        
        
        if ($vars['dateType']==1){
            $vars['startTime'] = date('Y-m-d',strtotime("-1 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==2){
            $vars['startTime'] = date('Y-m-d',strtotime("-8 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==3){
            $vars['startTime'] = date('Y-m-d',strtotime("-31 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==4){
            $vars['startTime'] = date('Y-m-d',strtotime("-3 months"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==5){
            $vars['startTime'] = '';
            $vars['endTime']   = '';
        }
        
        $urls = $vars;

        $wheres[] = "`productCode` = '{$this->_productCode}'";
        
        $fileNameNote = array();
        if ($vars['startTime'] != '') {
            $wheres[] = "`date` >= '{$vars['startTime']}' ";
            $fileNameNote[] = $vars['startTime'];
        }
        if ($vars['endTime'] != '') {
            $wheres[] = "`date` <= '{$vars['endTime']}' ";
            $fileNameNote[] = $vars['endTime'];
        }
        $fileNameNote[] = $this->_productCode;
        if ($vars['osName'] != '') {
            $wheres[] = "`osName` = '{$vars['osName']}' ";
            $fileNameNote[] = $vars['osName'];
        }
        if ($vars['source'] != '') {
            $wheres[] = "`sourceId` IN (SELECT  ms.`sourceId` FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON  ms.memberCode=mm.code   WHERE   mm.name='{$vars['source']}' AND ms.productCode='{$this->_productCode}') ";
            $fileNameNote[] = $vars['source'];
        }
        if ($vars['sourceId'] != '') {
            $wheres[] = "`sourceId` = '{$vars['sourceId']}' ";
            $fileNameNote[] = $vars['sourceId'];
        }
        
        
        
        
        $wheres[] ="`date`>='2011-10-13'";
        $orderBy = "`pv` DESC";

        //清理测试数据
        $wheres[] = $this->_whereByClear2;

        //获取总记录
        $where = count($wheres) > 0 ? ' WHERE ' . implode(' AND ', $wheres) . ' ' : '';
        $topictoorderModel = new Application_Model_ActDayDataTopictoorder();
        $sql = "SELECT eventPageId,eventPageName as pageName,pageId,sum(pv) as pv,sum(uv) as uv FROM `{$topictoorderModel->getTableName()}` {$where} GROUP BY eventPageId,eventPageName,pageId ORDER BY {$orderBy}";
        $topictoOrderRows = $topictoorderModel ->getAdapter()->fetchAll($sql);
        
        $topicvisitModel = new Application_Model_ActDayDataTopicvisit();
        $sql = "SELECT pageId,pageName,sum(pv) as pv,sum(uv) as uv FROM `{$topicvisitModel->getTableName()}` {$where} GROUP BY pageId,pageName ORDER BY {$orderBy}";
        $topicvisitRows = $topicvisitModel ->getAdapter()->fetchAll($sql);
        //筛选专题名
        if (!empty($vars['topicName'])) {
            $topicName = str_replace("\r\n",",",$vars['topicName']);
            $topicName = str_replace("|",",",$topicName);
            $topicNameArr = isset($topicName) ? explode(',', $topicName) : array();
            $reTopicNameArr = array();
            foreach($topicNameArr as $v) {
                $reTopicNameArr[]= trim($v);
            }
            foreach($topictoOrderRows as $key=>$row) {
           	    if (!in_array(trim($row['pageName']), $reTopicNameArr)){
           	        unset($topictoOrderRows[$key]);
           	    }
            }
            foreach($topicvisitRows as $key=>$row) {
           	    if (!in_array(trim($row['pageName']), $reTopicNameArr)){
           	        unset($topicvisitRows[$key]);
           	    }
            }
            
        }
        
        
        $reTopictoOrderRows = array();
        foreach($topictoOrderRows as $key=>$row) {
            $row['pageName'] = trim($row['pageName']);
            $reTopictoOrderRows[$row['pageName']]['pv'] = $row['pv'];
            $reTopictoOrderRows[$row['pageName']]['uv'] = $row['uv'];
        }
        
        
        $countTopic = array();
        $countTopic['pvTopic'] = 0;
        $countTopic['uvTopic'] = 0;
        foreach($topicvisitRows as $key=>$row) {
            $countTopic['pvTopic'] =  $countTopic['pvTopic'] + $row['pv'];
            $countTopic['uvTopic'] =  $countTopic['uvTopic'] + $row['uv'];
        }
        
        
        $reRows = array();
        foreach($topicvisitRows as $key=>$row) {
            $row['pageName'] = trim($row['pageName']);
            
             //所占比例 
            $reRows[$row['pageName']]['pvTopicPercent'] = $countTopic['pvTopic']>0 ? number_format($row['pv']/$countTopic['pvTopic'],4)*100 : 0;
            $reRows[$row['pageName']]['uvTopicPercent'] = $countTopic['uvTopic']>0 ? number_format($row['uv']/$countTopic['uvTopic'],4)*100 : 0;
            
            
            //专题浏览
            $reRows[$row['pageName']]['pageName'] = $row['pageName'];
            $reRows[$row['pageName']]['pvTopic'] = $row['pv'];
            $reRows[$row['pageName']]['uvTopic'] = $row['uv'];
            
            //订单
            $reRows[$row['pageName']]['pvOrder'] = 0;
            if(isset($reTopictoOrderRows[$row['pageName']]['pv'])){
                $reRows[$row['pageName']]['pvOrder'] = $reTopictoOrderRows[$row['pageName']]['pv'];
            }
            $reRows[$row['pageName']]['uvOrder'] = 0;
            if(isset($reTopictoOrderRows[$row['pageName']]['uv'])){
                $reRows[$row['pageName']]['uvOrder'] = $reTopictoOrderRows[$row['pageName']]['uv'];
            }
            
        }
        
        //重组
        //pv
        $reArray = array();
        $reOtherArray = array();
        $reOtherArray['other']['name'] = '';
        $reOtherArray['other']['pvTopicPercent'] = 0;
        $reOtherArray['other']['pvTopic'] = 0;
        $reOtherArray['other']['pvOrder'] = 0;
        $reOtherArray['other']['uvTopicPercent'] = 0;
        $reOtherArray['other']['uvTopic'] = 0;
        $reOtherArray['other']['uvOrder'] = 0;
        foreach ($reRows as $key => $row){
            if($row['pvTopicPercent']<0){
               $reOtherArray['other']['name'] = '其他';
               $reOtherArray['other']['pvTopic'] = $reOtherArray['other']['pvTopic'] + $row['pvTopic'];
               $reOtherArray['other']['pvOrder'] = $reOtherArray['other']['pvOrder'] + $row['pvOrder'];
               $reOtherArray['other']['uvTopic'] = $reOtherArray['other']['uvTopic'] + $row['uvTopic'];
               $reOtherArray['other']['uvOrder'] = $reOtherArray['other']['uvOrder'] + $row['uvOrder'];
            } else {
               $reArray[$row['pageName']]['name'] = $row['pageName'];
               $reArray[$row['pageName']]['pvTopic'] = $row['pvTopic'];
               $reArray[$row['pageName']]['pvOrder'] = $row['pvOrder'];
               $reArray[$row['pageName']]['uvTopic'] = $row['uvTopic'];
               $reArray[$row['pageName']]['uvOrder'] = $row['uvOrder'];
            }
            
        }
        if($reOtherArray['other']['pvTopic']>0){
            $reArray = array_merge($reArray,$reOtherArray);
        }
        
        $count = array();
        $count['pvTopic'] = 0;
        $count['uvTopic'] = 0;
        $count['pvOrder'] = 0;
        foreach($reArray as $var=>$row) {
            $count['pvTopic'] = $count['pvTopic'] + $row['pvTopic'];
            $count['uvTopic'] = $count['uvTopic'] + $row['uvTopic'];
            $count['pvOrder'] = $count['pvOrder'] + $row['pvOrder'];
        }
        $this->view->rows = $reArray;
        $this->view->count = $count;
        $this->view->vars = $vars;
         
        //操作系统
        $osNameRows = $this->_model ->getOsNameArray();
        $this->view->osNameRows = $osNameRows;
        
        //渠道列表
        $memberRows = $this->_model->getMemberArray($this->_productCode,$vars);
        $this->view->memberRows = $memberRows;
        
        //导出Excel
        if ($vars['export']=='yes'){
            $exportArray = array();
            $exportNum =1;
            foreach ($reArray as $row){
                $exportNum++;
                $exportArray['A'.$exportNum]= $row['name'];
                $exportArray['B'.$exportNum]= $row['pvTopic'];
                $exportArray['C'.$exportNum]= $row['uvTopic'];
                $exportArray['D'.$exportNum]= $row['pvOrder'];
            }
            $exportNum = $exportNum+1;
            $exportArray['A'.$exportNum]= '总计';
            $exportArray['B'.$exportNum]= $count['pvTopic'];
            $exportArray['C'.$exportNum]= $count['uvTopic'];
            $exportArray['D'.$exportNum]= $count['pvOrder'];
            
            $table = array();
            $table[0]['tableName'] = 'topic';
            $table[0]['title'] = array('专题名称','专题点击次数','专题点击人数','专题订单量');
            $table[0]['con'] = $exportArray;
            $fileName = count($fileNameNote) > 0 ? iconv('UTF-8','GBK//IGNORE','专题分析：'). implode('_', $fileNameNote): iconv('UTF-8','GBK//IGNORE','专题分析');
            $conversionModel = new Application_Model_ActDayDataConversion();
            $conversionModel->excelExport($table,$fileName);
        }
    }
    
    
    /**
     * 路径比例
     * 
     * @return void
     */
    public function pathAction()
    {

        
        //设置URL模板以及条件
        $urls = array();
        $vars = array();
        $wheres = array();

        
        $vars['osName'] = trim($this->_request->get('osName'));
        $vars['source'] = trim($this->_request->get('source'));
        $vars['sourceId'] = trim($this->_request->get('sourceId'));
        
        $vars['dateType']  = trim($this->_request->get('dateType'))!='' ? trim($this->_request->get('dateType')) : 1;
        $vars['doType'] = 1;//trim($this->_request->get('doType'))!='' ? trim($this->_request->get('doType')) : 1;
        $vars['startTime'] = trim($this->_request->get('startTime'));
        $vars['endTime'] = trim($this->_request->get('endTime'));
        
        if ($vars['dateType']==1){
            $vars['startTime'] = date('Y-m-d',strtotime("-1 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==2){
            $vars['startTime'] = date('Y-m-d',strtotime("-8 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==3){
            $vars['startTime'] = date('Y-m-d',strtotime("-31 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==4){
            $vars['startTime'] = date('Y-m-d',strtotime("-3 months"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==5){
            $vars['startTime'] = '';
            $vars['endTime']   = '';
        }
        
        $urls = $vars;
        $wheres[] = "`productCode` = '{$this->_productCode}'";
        
        if ($vars['startTime'] != '') {
            $wheres[] = "`date` >= '{$vars['startTime']}' ";
        }
        if ($vars['endTime'] != '') {
            $wheres[] = "`date` <= '{$vars['endTime']}' ";
        }
        if ($vars['osName'] != '') {
            $wheres[] = "`osName` = '{$vars['osName']}' ";
        }
        if ($vars['source'] != '') {
            $wheres[] = "`sourceId` IN (SELECT  ms.`sourceId` FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON  ms.memberCode=mm.code   WHERE   mm.name='{$vars['source']}' AND ms.productCode='{$this->_productCode}') ";
        }
        if ($vars['sourceId'] != '') {
            $wheres[] = "`sourceId` = '{$vars['sourceId']}' ";
        }
        
        $wheres[] ="`date`>='2011-10-13'";
        $orderBy = "`pv` DESC";

        //清理测试数据
        $wheres[] = $this->_whereByClear2;

        //获取总记录
        $where = count($wheres) > 0 ? ' WHERE ' . implode(' AND ', $wheres) . ' ' : '';
        $reArray = array();
        $pagevisitMode = new Application_Model_ActDayDataPagevisit();
        $sql = "SELECT pageId,sum(pv) as pv,sum(uv) as uv FROM `{$pagevisitMode->getTableName()}`  {$where} AND `pageId`='1002'  GROUP BY pageId ORDER BY {$orderBy} limit 1";
        $indexRow = $pagevisitMode ->getAdapter()->fetchRow($sql);
        
        $index = 'index' ;
        if($indexRow){
            $reArray[$index]['eventPageId'] = 0;
            $reArray[$index]['pageId'] = $indexRow['pageId'];
            $reArray[$index]['pv'] = $indexRow['pv'];
            $reArray[$index]['uv'] = $indexRow['uv'];
        }else {
            $reArray = array($index =>array('eventPageId'=>'0','pageId'=>'1002','pv'=>0,'uv'=>0));
        }
        
        $homevisitModel = new Application_Model_ActDayDataHomevisit();
        $sql = "SELECT eventPageId,pageId,sum(pv) as pv,sum(uv) as uv FROM `{$homevisitModel->getTableName()}` {$where} GROUP BY eventPageId,pageId ORDER BY {$orderBy}";
        $homevisitRows = $homevisitModel ->getAdapter()->fetchAll($sql);
        //dump($homevisitRows);
        $flagArray = array('1020','1005','1009','1022','1023','1011');
        //首页[1002],专题[1020],分类[1005],搜索[1009],抢购[1022],秒杀[1023],购物车页[1011]
        $other = 'more';
        $reArray[$other] = array('eventPageId'=>'1002','pageId'=>$other,'pv'=>0,'uv'=>0);
        $lost = 'lost';
        $reArray[$lost] = array('eventPageId'=>'1002','pageId'=>$lost,'pv'=>0,'uv'=>0);
        foreach ($homevisitRows as $row){
            if (in_array($row['pageId'],$flagArray)){
                foreach ($flagArray as $element){
                    if ($row['pageId'] == $element){
                        $reArray[$element]['eventPageId'] = $row['eventPageId'];
                        $reArray[$element]['pageId'] = trim($row['pageId']);
                        $reArray[$element]['pv'] = $row['pv'];
                        $reArray[$element]['uv'] = $row['uv'];
                    }
                     
                }
            } else {
                $reArray[$other]['pv'] = $reArray[$other]['pv']+$row['pv'];
                $reArray[$other]['uv'] = $reArray[$other]['uv']+$row['uv'];
            }
        }
        
        $emptyArray = array();
        foreach ($flagArray as $element){
            if (!isset($reArray[$element])){
                $emptyArray =  $emptyArray + array($element =>array('eventPageId'=>'1002','pageId'=>$element,'pv'=>0,'uv'=>0));
            }
        }
        
        $reArray = $reArray+$emptyArray;
        
        $countArray = array();
        $countArray['pv'] = 0;
        $countArray['uv'] = 0;
        foreach ($homevisitRows as $row){
            $countArray['pv'] = $countArray['pv']+$row['pv'];
            $countArray['uv'] = $countArray['uv']+$row['uv'];
        }
        //lost 用pv计算,uv为负值
        $reArray[$lost]['pv'] = $reArray[$index]['pv'] - $countArray['pv'];
        $reArray[$lost]['pv'] = $reArray[$lost]['pv']>0 ? $reArray[$lost]['pv'] : 0;
        $reArray[$lost]['uv'] = $reArray[$index]['uv'] - $countArray['uv'];
        $reArray[$lost]['uv'] = $reArray[$lost]['uv']>0 ? $reArray[$lost]['uv'] : 0; 
        
        //dump($reArray);
        $this->view->rows = $reArray;
        $this->view->vars = $vars;
         
        //操作系统
        $osNameRows = $this->_model ->getOsNameArray();
        $this->view->osNameRows = $osNameRows;
        
        //渠道列表
        $memberRows = $this->_model->getMemberArray($this->_productCode,$vars);
        $this->view->memberRows = $memberRows;
        
        
    }
    
    
    
    /**
     * 基础漏斗分析
     * 
     * @return void
     */
    public function fromCountAction()
    {

        
        //设置URL模板以及条件
        $urls = array();
        $vars = array();
        $wheres = array();

        
        $vars['osName'] = trim($this->_request->get('osName'));
        $vars['source'] = trim($this->_request->get('source'));
        $vars['sourceId'] = trim($this->_request->get('sourceId'));
        
        $vars['dateType']  = trim($this->_request->get('dateType'))!='' ? trim($this->_request->get('dateType')) : 1;
        $vars['doType'] = 1;//trim($this->_request->get('doType'))!='' ? trim($this->_request->get('doType')) : 1;
        $vars['startTime'] = trim($this->_request->get('startTime'));
        $vars['endTime'] = trim($this->_request->get('endTime'));
        
        if ($vars['dateType']==1){
            $vars['startTime'] = date('Y-m-d',strtotime("-1 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==2){
            $vars['startTime'] = date('Y-m-d',strtotime("-8 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==3){
            $vars['startTime'] = date('Y-m-d',strtotime("-31 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==4){
            $vars['startTime'] = date('Y-m-d',strtotime("-3 months"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==5){
            $vars['startTime'] = '';
            $vars['endTime']   = '';
        }
        
        $urls = $vars;
        $wheres[] = "`productCode` = '{$this->_productCode}'";
        
        if ($vars['startTime'] != '') {
            $wheres[] = "`date` >= '{$vars['startTime']}' ";
        }
        if ($vars['endTime'] != '') {
            $wheres[] = "`date` <= '{$vars['endTime']}' ";
        }
        if ($vars['osName'] != '') {
            $wheres[] = "`osName` = '{$vars['osName']}' ";
        }
        if ($vars['source'] != '') {
            $wheres[] = "`sourceId` IN (SELECT  ms.`sourceId` FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON  ms.memberCode=mm.code   WHERE   mm.name='{$vars['source']}' AND ms.productCode='{$this->_productCode}') ";
        }
        if ($vars['sourceId'] != '') {
            $wheres[] = "`sourceId` = '{$vars['sourceId']}' ";
        }
        
        $wheres[] ="`date`>='2011-10-13'";
        $orderBy = "`pv` DESC";

        //清理测试数据
        $wheres[] = $this->_whereByClear2;

        //获取总记录
        $where = count($wheres) > 0 ? ' WHERE ' . implode(' AND ', $wheres) . ' ' : '';
        $reArray = array();
        
        //pagevisit 首页/商品详情/结算中心
        $pageArray = array('1002','1007','1012');
        $pageIdStr = count($pageArray) > 0 ? '  AND `pageId` in ( ' . implode(' , ', $pageArray) . ' )' : '';
        $pagevisitMode = new Application_Model_ActDayDataPagevisit();
        $sql = "SELECT pageId,sum(pv) as pv,sum(uv) as uv FROM `{$pagevisitMode->getTableName()}`  {$where} {$pageIdStr}  GROUP BY pageId ORDER BY {$orderBy}";
        $pageRows = $pagevisitMode ->getAdapter()->fetchAll($sql);
        
        foreach ($pageRows as $row){
            if (in_array($row['pageId'],$pageArray)){
                foreach ($pageArray as $element){
                    if ($row['pageId'] == $element){
                        $reArray['1004-'.$element]['eventPageId'] = '0';
                        $reArray['1004-'.$element]['pageId'] = trim($row['pageId']);
                        $reArray['1004-'.$element]['pv'] = $row['pv'];
                        $reArray['1004-'.$element]['uv'] = $row['uv'];
                    }
                     
                }
            }
        }
        
        $emptyArray = array();
        foreach ($pageArray as $element){
            if (!isset($reArray['1004-'.$element])){
                $emptyArray =  $emptyArray + array('1004-'.$element =>array('eventPageId'=>'0','pageId'=>$element,'pv'=>0,'uv'=>0));
            }
        }
        $reArray = $reArray+$emptyArray;
        
        //conversion 加入购物车/提交订单
        $pageArray = array('1003','1007');
        $pageIdStr = count($pageArray) > 0 ? '  AND `pageId` in ( ' . implode(' , ', $pageArray) . ' )' : '';
        $conversionMode = new Application_Model_ActDayDataConversion();
        $sql = "SELECT pageId,sum(pv) as pv,sum(uv) as uv FROM `{$conversionMode->getTableName()}`  {$where} {$pageIdStr} GROUP BY pageId ORDER BY {$orderBy}";
        $conversionRows = $conversionMode ->getAdapter()->fetchAll($sql);
        
        foreach ($conversionRows as $row){
            if (in_array($row['pageId'],$pageArray)){
                foreach ($pageArray as $element){
                    if ($row['pageId'] == $element){
                        $reArray['1005-'.$element]['eventPageId'] = '0';
                        $reArray['1005-'.$element]['pageId'] = trim($row['pageId']);
                        $reArray['1005-'.$element]['pv'] = $row['pv'];
                        $reArray['1005-'.$element]['uv'] = $row['uv'];
                    }
                     
                }
            }
        }
        
        $emptyArray = array();
        foreach ($pageArray as $element){
            if (!isset($reArray['1005-'.$element])){
                $emptyArray =  $emptyArray + array('1005-'.$element =>array('eventPageId'=>'0','pageId'=>$element,'pv'=>0,'uv'=>0));
            }
        }
        $reArray = $reArray+$emptyArray;
        
        
        //fromvist 其他
        $fromPageArray = array('1007','1012');
        $fromPageIdStr = count($fromPageArray) > 0 ? '  AND `eventPageId` in ( ' . implode(' , ', $fromPageArray) . ' )' : '';
        $fromvisitModel = new Application_Model_ActDayDataFromvisit();
        $sql = "SELECT eventPageId,pageId,sum(pv) as pv,sum(uv) as uv FROM `{$fromvisitModel->getTableName()}` {$where} {$fromPageIdStr} GROUP BY eventPageId,pageId ORDER BY {$orderBy}";
        $fromvisitRows = $fromvisitModel ->getAdapter()->fetchAll($sql);
        
        foreach ($fromPageArray as $element){
            $reArray['1004-'.$element.'-count'] = array('eventPageId'=>$element,'pageId'=>'count','pv'=>0,'uv'=>0);
            $reArray['1004-'.$element.'-other'] = array('eventPageId'=>$element,'pageId'=>'other','pv'=>0,'uv'=>0);
        }
        foreach ($fromvisitRows as $row){
            foreach ($fromPageArray as $element){
                if ($row['eventPageId'] == $element){
                    //商品详情
                    if ($row['eventPageId']== '1007' &&$row['pageId']!='1011'){
                        $reArray['1004-1007-other']['pv'] = $reArray['1004-1007-other']['pv']+$row['pv'];
                        $reArray['1004-1007-other']['uv'] = $reArray['1004-1007-other']['uv']+$row['uv'];
                    }
                    if ($row['eventPageId']== '1007'){
                        $reArray['1004-1007-count']['pv'] = $reArray['1004-1007-count']['pv']+$row['pv'];
                        $reArray['1004-1007-count']['uv'] = $reArray['1004-1007-count']['uv']+$row['uv'];
                    }
                    //结算中心
                    if ($row['eventPageId']== '1012' &&$row['pageId']!='1013'){
                        $reArray['1004-1012-other']['pv'] = $reArray['1004-1012-other']['pv']+$row['pv'];
                        $reArray['1004-1012-other']['uv'] = $reArray['1004-1012-other']['uv']+$row['uv'];
                    }
                    if ($row['eventPageId']== '1012'){
                        $reArray['1004-1012-count']['pv'] = $reArray['1004-1012-count']['pv']+$row['pv'];
                        $reArray['1004-1012-count']['uv'] = $reArray['1004-1012-count']['uv']+$row['uv'];
                    }
                }
            }
        }
        
        //dump($reArray);
        
        $this->view->rows = $reArray;
        $this->view->vars = $vars;
         
        //操作系统
        $osNameRows = $this->_model ->getOsNameArray();
        $this->view->osNameRows = $osNameRows;
        
        //渠道列表
        $memberRows = $this->_model->getMemberArray($this->_productCode,$vars);
        $this->view->memberRows = $memberRows;
        
        
    }
    
    
    
    
     /**
     * 商品分析
     * 
     * @return void
     */
    public function productAction()
    {

        //获取当前页码
        $pageSize = 20;
        $pageNo = intval($this->_request->get('pageNo'));
        if (empty($pageNo)) {
            $pageNo = 1;
        }
        //设置URL模板以及条件
        $urls = array();
        $vars = array();
        $wheres = array();

        
        $vars['osName'] = trim($this->_request->get('osName'));
        $vars['source'] = trim($this->_request->get('source'));
        $vars['sourceId'] = trim($this->_request->get('sourceId'));
        
        $vars['dateType']  = trim($this->_request->get('dateType'))!='' ? trim($this->_request->get('dateType')) : 1;
        $vars['doType'] = trim($this->_request->get('doType'))!='' ? trim($this->_request->get('doType')) : 'amount';
        $vars['startTime'] = trim($this->_request->get('startTime'));
        $vars['endTime'] = trim($this->_request->get('endTime'));
        
        if ($vars['dateType']==1){
            $vars['startTime'] = date('Y-m-d',strtotime("-1 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==2){
            $vars['startTime'] = date('Y-m-d',strtotime("-8 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==3){
            $vars['startTime'] = date('Y-m-d',strtotime("-31 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==4){
            $vars['startTime'] = date('Y-m-d',strtotime("-3 months"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==5){
            $vars['startTime'] = '';
            $vars['endTime']   = '';
        }
        
        $urls = $vars;

        $wheres[] = "`productCode` = '{$this->_productCode}'";
        
        if ($vars['startTime'] != '') {
            $wheres[] = "`date` >= '{$vars['startTime']}' ";
        }
        if ($vars['endTime'] != '') {
            $wheres[] = "`date` <= '{$vars['endTime']}' ";
        }
        if ($vars['osName'] != '') {
            $wheres[] = "`osName` = '{$vars['osName']}' ";
        }
        if ($vars['source'] != '') {
            $wheres[] = "`sourceId` IN (SELECT  ms.`sourceId` FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON  ms.memberCode=mm.code   WHERE   mm.name='{$vars['source']}' AND ms.productCode='{$this->_productCode}') ";
        }
        if ($vars['sourceId'] != '') {
            $wheres[] = "`sourceId` = '{$vars['sourceId']}' ";
        }
        
        $wheres[] ="`date`>='2011-10-13'";
        $orderBy = $vars['doType'];

        //清理测试数据
        $wheres[] = $this->_whereByClear2;

        $urls['pageNo'] = '{page}';
        $urlTemplate = $this->view->projectUrl($urls);
        
        //获取总记录
        $where = count($wheres) > 0 ? ' WHERE ' . implode(' AND ', $wheres) . ' ' : '';
        $productModel = new Application_Model_ActDayDataProduct();
        $sql = "SELECT sum(pv) as pv,sum(uv) as uv,sum(orderNum) as orderNum,sum(amount) as amount FROM `{$productModel->getTableName()}` {$where} ";
        $productRows = $productModel ->getAdapter()->fetchRow($sql);
        
        $sql = "SELECT pageId,pageName,sum(pv) as pv,sum(uv) as uv,sum(orderNum) as orderNum,sum(amount) as amount FROM `{$productModel->getTableName()}` {$where} GROUP BY pageId,pageName ORDER BY {$orderBy} DESC";
        //echo $sql;
        $dbPaginator = new Application_Model_DbPaginator($sql, $pageSize, $pageNo);
        $recordCount = $dbPaginator->getRecodCount();
        
        //获取分页html字符
        Zend_Loader::loadClass('Project_Paginator');
        $paginator = new Project_Paginator($recordCount, $pageSize, $pageNo);
        $paginator->urlTemplateContent = $urlTemplate;

        $rows = $dbPaginator->getRows();
        $reRows = array();
        foreach($rows as $var=>$row) {
            $pvPercent = $productRows['pv']>0 ? number_format($row['pv']/$productRows['pv'],4)*100 : 0 ;
            $pvPercent = $pvPercent>100 ? 100 : $pvPercent ;
            $pvPercentInfo =  $pvPercent."%";
            $uvPercent = $productRows['uv']>0 ? number_format($row['uv']/$productRows['uv'],4)*100 : 0 ;
            $uvPercent = $uvPercent>100 ? 100 : $uvPercent ;
            $uvPercentInfo =  $uvPercent."%";
            
            $reRows[$var]['pageName'] = $row['pageName'];
            $reRows[$var]['uv'] = $row['uv'];
            $reRows[$var]['uvPercentInfo'] = $uvPercentInfo;
            $reRows[$var]['pv'] = $row['pv'];
            $reRows[$var]['pvPercentInfo'] = $pvPercentInfo;
            $reRows[$var]['orderNum'] = $row['orderNum'];
            $reRows[$var]['amount'] = floatval($row['amount'])>0 ? number_format(floatval($row['amount']),2) : 0 ;
            
        }

        //分配view(分页处理后的记录集以及分页HTML字符)
        $this->view->pageString = $paginator->getPageString();
        $this->view->count = $productRows;
        $this->view->rows = $reRows;
        $urls['pageNo'] = $pageNo;
        $this->view->pageUrl = $this->view->projectUrl($urls);
        $this->view->vars = $vars;
         
        //操作系统
        $osNameRows = $this->_model ->getOsNameArray();
        $this->view->osNameRows = $osNameRows;
        
        //渠道列表
        $memberRows = $this->_model->getMemberArray($this->_productCode,$vars);
        $this->view->memberRows = $memberRows;
        
        
    }
    
    
    /**
     * 商品分析 导出
     * 
     * @return void
     */
    public function productExportAction()
    {
        //设置URL模板以及条件
        $urls = array();
        $vars = array();
        $wheres = array();

        
        $vars['osName'] = trim($this->_request->get('osName'));
        $vars['source'] = trim($this->_request->get('source'));
        $vars['sourceId'] = trim($this->_request->get('sourceId'));
        
        $vars['dateType']  = trim($this->_request->get('dateType'))!='' ? trim($this->_request->get('dateType')) : 1;
        $vars['doType'] = trim($this->_request->get('doType'))!='' ? trim($this->_request->get('doType')) : 'amount';
        $vars['startTime'] = trim($this->_request->get('startTime'));
        $vars['endTime'] = trim($this->_request->get('endTime'));
        
        if ($vars['dateType']==1){
            $vars['startTime'] = date('Y-m-d',strtotime("-1 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==2){
            $vars['startTime'] = date('Y-m-d',strtotime("-8 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==3){
            $vars['startTime'] = date('Y-m-d',strtotime("-31 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==4){
            $vars['startTime'] = date('Y-m-d',strtotime("-3 months"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==5){
            $vars['startTime'] = '';
            $vars['endTime']   = '';
        }
        
        $urls = $vars;

        $wheres[] = "`productCode` = '{$this->_productCode}'";
        
        $fileNameNote = array();
        if ($vars['startTime'] != '') {
            $wheres[] = "`date` >= '{$vars['startTime']}' ";
            $fileNameNote[] = $vars['startTime'];
        }
        if ($vars['endTime'] != '') {
            $wheres[] = "`date` <= '{$vars['endTime']}' ";
            $fileNameNote[] = $vars['endTime'];
        }
        $fileNameNote[] = $this->_productCode;
        if ($vars['osName'] != '') {
            $wheres[] = "`osName` = '{$vars['osName']}' ";
            $fileNameNote[] = $vars['osName'];
        }
        if ($vars['source'] != '') {
            $wheres[] = "`sourceId` IN (SELECT  ms.`sourceId` FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON  ms.memberCode=mm.code   WHERE   mm.name='{$vars['source']}' AND ms.productCode='{$this->_productCode}') ";
            $fileNameNote[] = $vars['source'];
        }
        if ($vars['sourceId'] != '') {
            $wheres[] = "`sourceId` = '{$vars['sourceId']}' ";
            $fileNameNote[] = $vars['sourceId'];
        }
        
        $wheres[] ="`date`>='2011-10-13'";
        $orderBy = $vars['doType'];

        //清理测试数据
        $wheres[] = $this->_whereByClear2;

        
        //获取总记录
        $where = count($wheres) > 0 ? ' WHERE ' . implode(' AND ', $wheres) . ' ' : '';
        $productModel = new Application_Model_ActDayDataProduct();
        $sql = "SELECT sum(pv) as pv,sum(uv) as uv,sum(orderNum) as orderNum,sum(amount) as amount FROM `{$productModel->getTableName()}` {$where} ";
        $productRows = $productModel ->getAdapter()->fetchRow($sql);
        
        $sql = "SELECT pageId,pageName,sum(pv) as pv,sum(uv) as uv,sum(orderNum) as orderNum,sum(amount) as amount FROM `{$productModel->getTableName()}` {$where} GROUP BY pageId,pageName ORDER BY {$orderBy} DESC";
        $rows = $productModel ->getAdapter()->fetchAll($sql);
        
        $reRows = array();
        foreach($rows as $var=>$row) {
            $pvPercent = $productRows['pv']>0 ? number_format($row['pv']/$productRows['pv'],4)*100 : 0 ;
            $pvPercent = $pvPercent>100 ? 100 : $pvPercent ;
            $pvPercentInfo =  $pvPercent."%";
            $uvPercent = $productRows['uv']>0 ? number_format($row['uv']/$productRows['uv'],4)*100 : 0 ;
            $uvPercent = $uvPercent>100 ? 100 : $uvPercent ;
            $uvPercentInfo =  $uvPercent."%";
            
            $reRows[$var]['pageName'] = $row['pageName'];
            $reRows[$var]['uv'] = $row['uv'];
            $reRows[$var]['uvPercentInfo'] = $uvPercentInfo;
            $reRows[$var]['pv'] = $row['pv'];
            $reRows[$var]['pvPercentInfo'] = $pvPercentInfo;
            $reRows[$var]['orderNum'] = $row['orderNum'];
            $reRows[$var]['amount'] = floatval($row['amount'])>0 ? number_format(floatval($row['amount']),2) : 0 ;
            
        }
        
        
        //写入EXCEL文件
        $objPHPExcel = new PHPExcel_PHPExcel();
        $objPHPExcel->getProperties()->setCreator("Yek BI System")
                                     ->setLastModifiedBy("Yek BI")
                                     ->setTitle("product analysis")
                                     ->setSubject("product analysis")
                                     ->setDescription("")
                                     ->setKeywords("")
                                     ->setCategory("");
         $objActSheet = $objPHPExcel->getActiveSheet();
         
         //设置当前活动的sheet
         $objPHPExcel->setActiveSheetIndex(0);
         //设置sheet名字
         $objActSheet->setTitle('product');
         
         
         //设置单元格样式 
         //设置宽度   
         $objActSheet->getColumnDimension('A')->setWidth(60);
         $objActSheet->getColumnDimension('B')->setWidth(14);
         $objActSheet->getColumnDimension('C')->setWidth(21);
         $objActSheet->getColumnDimension('D')->setWidth(14);
         $objActSheet->getColumnDimension('E')->setWidth(21);
         $objActSheet->getColumnDimension('F')->setWidth(9);
         $objActSheet->getColumnDimension('G')->setWidth(13);
         //设置默认行高
         //$objActSheet->getDefaultRowDimension()->setRowHeight(20);
         //设置指定行高
         $objActSheet->getRowDimension(1)->setRowHeight(20);
          

        $keyArr = array('商品名称','商品点击人数','商品点击人数占比(%)','商品点击次数','商品点击次数占比(%)','订单数量','订单金额(￥)');
        $startElem = 'A';
        $i=1;
        foreach ($keyArr as $name) {
            $objPHPExcel->getActiveSheet()->setCellValue($startElem.$i, $name);
            //设置标题样式
            $objStyleTitle = $objActSheet->getStyle($startElem.$i);
            //设置字体   
            $objFontTitle = $objStyleTitle->getFont();   
            $objFontTitle->setName('Courier New');   
            $objFontTitle->setSize(10);   
            $objFontTitle->setBold(true);   
            //$objFontTitle->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);   
            $objFontTitle->getColor()->setARGB('FFFFFFFF'); 
            //设置填充颜色   
            $objFillTitle = $objStyleTitle->getFill();   
            $objFillTitle->setFillType(PHPExcel_Style_Fill::FILL_SOLID);   
            $objFillTitle->getStartColor()->setARGB('FF484848'); 
            
            $startElem++;
        }
        
        
        foreach ($reRows as $row) {
            $i++;
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $row['pageName']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $row['uv']);
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $row['uvPercentInfo']);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $row['pv']);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $row['pvPercentInfo']);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $row['orderNum']);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$i, $row['amount'],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $fileName = count($fileNameNote) > 0 ? iconv('UTF-8','GBK//IGNORE','商品分析：'). implode('_', $fileNameNote): iconv('UTF-8','GBK//IGNORE','商品分析');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $outputFileName = $fileName.".xls";
        //到浏览器  
        header("Content-Type: application/force-download");  
        header("Content-Type: application/octet-stream");  
        header("Content-Type: application/download");  
        header('Content-Disposition:inline;filename="'.$outputFileName.'"');  
        header("Content-Transfer-Encoding: binary");  
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");  
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");  
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
        header("Pragma: no-cache");
        $objWriter->save('php://output');
        exit;
    }
    
   /**
     * PUSH分析
     * 
     * @return void
     */
    public function pushAction()
    {

        
        //设置URL模板以及条件
        $urls = array();
        $vars = array();
        $wheres = array();
        $wheres2 = array();
        
        $vars['osName'] = trim($this->_request->get('osName'));
        $vars['source'] = trim($this->_request->get('source'));
        $vars['sourceId'] = trim($this->_request->get('sourceId'));
        
        $vars['dateType']  = trim($this->_request->get('dateType'))!='' ? trim($this->_request->get('dateType')) : 1;
        $vars['doType'] = 1;//trim($this->_request->get('doType'))!='' ? trim($this->_request->get('doType')) : 1;
        $vars['startTime'] = trim($this->_request->get('startTime'));
        $vars['endTime'] = trim($this->_request->get('endTime'));
        $vars['export']   = trim($this->_request->get('export'))!='' ? trim($this->_request->get('export')) :'no';
        
        if ($vars['dateType']==1){
            $vars['startTime'] = date('Y-m-d',strtotime("-1 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==2){
            $vars['startTime'] = date('Y-m-d',strtotime("-8 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==3){
            $vars['startTime'] = date('Y-m-d',strtotime("-31 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==4){
            $vars['startTime'] = date('Y-m-d',strtotime("-3 months"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==5){
            $vars['startTime'] = '';
            $vars['endTime']   = '';
        }
        
        $urls = $vars;

        $wheres[] = "`productCode` = '{$this->_productCode}'";
        $wheres2[] = "a.`productCode` = '{$this->_productCode}'";
        if ($vars['startTime'] != '') {
            $wheres[] = "`date` >= '{$vars['startTime']}' ";
        }
        if ($vars['endTime'] != '') {
            $wheres[] = "`date` <= '{$vars['endTime']}' ";
        }
        $fileNameNote[] = $this->_productCode;
        if ($vars['osName'] != '') {
            $wheres[] = "`osName` = '{$vars['osName']}' ";
            $wheres2[] = "a.`osName` = '{$vars['osName']}' ";
        }
        /*if ($vars['source'] != '') {
            $wheres[] = "`sourceId` IN (SELECT  ms.`sourceId` FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON  ms.memberCode=mm.code   WHERE   mm.name='{$vars['source']}' AND ms.productCode='{$this->_productCode}') ";
        }
        if ($vars['sourceId'] != '') {
            $wheres[] = "`sourceId` = '{$vars['sourceId']}' ";
        }*/
        
        $wheres[] ="`date`>='2011-10-13'";
        $orderBy = "`date` DESC"; 

        //清理测试数据
        $wheres[] = $this->_whereByClear2;

        //获取总记录
        $where = count($wheres) > 0 ? ' WHERE ' . implode(' AND ', $wheres) . ' ' : '';
        $where2 = count($wheres2) > 0 ? ' WHERE ' . implode(' AND ', $wheres2) . ' ' : '';
        $pushtoorderModel = new Application_Model_ActDayDataPushtoorder();
        $versionDayDataModel = new Application_Model_ClientVersionDayData();
        
        $sql = "SELECT date,sum(pushNum) as pushNum,sum(pushOrderNum) as pushOrderNum FROM `{$pushtoorderModel->getTableName()}`  {$where}  GROUP BY date ORDER BY {$orderBy}";
        $rows = $pushtoorderModel ->getAdapter()->fetchAll($sql);
        //可以发push的APP版本的装机数[用户数]
        $sql = "SELECT a.date,sum(b.installNum) as installNum  FROM `{$pushtoorderModel->getTableName()}` AS a LEFT JOIN  `{$versionDayDataModel->getTableName()}` AS b ON a.productCode =b.productCode AND a.osName=b.osName AND a.version=b.version AND a.date=b.date {$where2}  GROUP BY a.date ORDER BY a.`date` DESC";
        $versionDayDataRows = $pushtoorderModel ->getAdapter()->fetchAll($sql);
        
        $reRows = array();
        foreach($rows as $key=>$row) {
            $reRows[$row['date']]['date'] = $row['date'];
            $pushAccept = 0;
            foreach($versionDayDataRows as $var=>$element) {
                if (isset($element['date'])&&$element['date']<=$row['date']){
                    $element['installNum'] = $element['installNum'] ? $element['installNum'] :0;
                    $pushAccept = $pushAccept +$element['installNum'];
                }
            }
            $reRows[$row['date']]['pushAccept'] = $pushAccept;
            $reRows[$row['date']]['pushOpen'] = $row['pushNum'];
             //占比
            $reRows[$row['date']]['pushPercent'] = $reRows[$row['date']]['pushAccept']>0 ? number_format($reRows[$row['date']]['pushOpen']/$reRows[$row['date']]['pushAccept'],4)*100 : 0;
            $reRows[$row['date']]['pushPercent'] = $reRows[$row['date']]['pushPercent']>100 ? 100 :$reRows[$row['date']]['pushPercent'];
            $reRows[$row['date']]['pushPercent'] = $reRows[$row['date']]['pushPercent'].'%';
           
            $reRows[$row['date']]['pushOrderNum'] = $row['pushOrderNum'];
            //转化率
            $reRows[$row['date']]['conversionPercent'] = $reRows[$row['date']]['pushOpen']>0 ? number_format($reRows[$row['date']]['pushOrderNum']/$reRows[$row['date']]['pushOpen'],4)*100 : 0;
            $reRows[$row['date']]['conversionPercent'] = $reRows[$row['date']]['conversionPercent']>100 ? 100 :$reRows[$row['date']]['conversionPercent'];
            $reRows[$row['date']]['conversionPercent'] = $reRows[$row['date']]['conversionPercent'].'%';
        }
        
        $count = array();
        $count['pushAccept'] = 0;
        $count['pushOpen'] = 0;
        $count['pushPercent'] = 0;
        $count['pushOrderNum'] = 0;
        $count['conversionPercent'] = 0;
        foreach($reRows as $var=>$row) {
            $count['pushAccept'] = $count['pushAccept'] + $row['pushAccept'];
            $count['pushOpen'] = $count['pushOpen'] + $row['pushOpen'];
            $count['pushPercent']= $count['pushAccept']>0 ? number_format($count['pushOpen']/$count['pushAccept'],4)*100 : 0;
            $count['pushPercent'] = $count['pushPercent']>100 ? 100 :$count['pushPercent'];
            $count['pushPercent'] = $count['pushPercent'].'%';
            $count['pushOrderNum'] = $count['pushOrderNum'] + $row['pushOrderNum'];
            $count['conversionPercent'] = $count['pushOpen']>0 ? number_format($count['pushOrderNum']/$count['pushOpen'],4)*100 : 0;
            $count['conversionPercent'] = $count['conversionPercent']>100 ? 100 :$count['conversionPercent'];
            $count['conversionPercent'] = $count['conversionPercent'].'%';            
        }
        $this->view->rows = $reRows;
        $this->view->count = $count;
        $this->view->vars = $vars;
         
        //操作系统
        $osNameRows = $this->_model ->getOsNameArray();
        $this->view->osNameRows = $osNameRows;
        
        //渠道列表
        $memberRows = $this->_model->getMemberArray($this->_productCode,$vars);
        $this->view->memberRows = $memberRows;
        
    }
    
    
	/**
     * 首页类型[A|B]=>订单分析
     * 
     * @return void
     */
    public function indexTypeAction()
    {

        
        //设置URL模板以及条件
        $urls = array();
        $vars = array();
        $wheres = array();
        $wheres2 = array();
        
        $vars['osName'] = trim($this->_request->get('osName'));
        $vars['source'] = trim($this->_request->get('source'));
        $vars['sourceId'] = trim($this->_request->get('sourceId'));
        
        $vars['dateType']  = trim($this->_request->get('dateType'))!='' ? trim($this->_request->get('dateType')) : 1;
        $vars['doType'] = 1;//trim($this->_request->get('doType'))!='' ? trim($this->_request->get('doType')) : 1;
        $vars['startTime'] = trim($this->_request->get('startTime'));
        $vars['endTime'] = trim($this->_request->get('endTime'));
        $vars['export']   = trim($this->_request->get('export'))!='' ? trim($this->_request->get('export')) :'no';
        
        if ($vars['dateType']==1){
            $vars['startTime'] = date('Y-m-d',strtotime("-1 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==2){
            $vars['startTime'] = date('Y-m-d',strtotime("-8 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==3){
            $vars['startTime'] = date('Y-m-d',strtotime("-31 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==4){
            $vars['startTime'] = date('Y-m-d',strtotime("-3 months"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==5){
            $vars['startTime'] = '';
            $vars['endTime']   = '';
        }
        
        $urls = $vars;

        $wheres[] = "`productCode` = '{$this->_productCode}'";
        $wheres2[] = "a.`productCode` = '{$this->_productCode}'";
        if ($vars['startTime'] != '') {
            $wheres[] = "`date` >= '{$vars['startTime']}' ";
        }
        if ($vars['endTime'] != '') {
            $wheres[] = "`date` <= '{$vars['endTime']}' ";
        }
        $fileNameNote[] = $this->_productCode;
        if ($vars['osName'] != '') {
            $wheres[] = "`osName` = '{$vars['osName']}' ";
            $wheres2[] = "a.`osName` = '{$vars['osName']}' ";
        }
        if ($vars['source'] != '') {
            $wheres[] = "`sourceId` IN (SELECT  ms.`sourceId` FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON  ms.memberCode=mm.code   WHERE   mm.name='{$vars['source']}' AND ms.productCode='{$this->_productCode}') ";
        }
        if ($vars['sourceId'] != '') {
            $wheres[] = "`sourceId` = '{$vars['sourceId']}' ";
        }
        
        $wheres[] ="`date`>='2011-10-13'";
        $orderBy = "`date` DESC"; 

        //清理测试数据
        $wheres[] = $this->_whereByClear2;

        //获取总记录
        $where = count($wheres) > 0 ? ' WHERE ' . implode(' AND ', $wheres) . ' ' : '';
        $where2 = count($wheres2) > 0 ? ' WHERE ' . implode(' AND ', $wheres2) . ' ' : '';
        $indextoorderModel = new Application_Model_ActDayDataIndextoorder();
        
        $sql = "SELECT date,indexType,sum(pv) as pv,sum(orderNum) as orderNum FROM `{$indextoorderModel->getTableName()}`  {$where}  GROUP BY date,indexType ORDER BY {$orderBy}";
        $rows = $indextoorderModel ->getAdapter()->fetchAll($sql);
        //列出日期
        $dateArray = array();
        foreach($rows as $key=>$row) {
            if(!in_array($row['date'], $dateArray)) {
            	$dateArray[] = $row['date'];
            }
        }
        //重组
        $reRows = array();
        foreach($dateArray as $date) {
        	$reRows[$row['date']]['A-pv'] = 0;
        	$reRows[$row['date']]['A-orderNum'] = 0;
        	$reRows[$row['date']]['B-pv'] = 0;
        	$reRows[$row['date']]['B-orderNum'] = 0;
	        foreach($rows as $key=>$row) {
	        	if (isset($row['date']) && $date==$row['date']){
	        		$reRows[$row['date']]['date'] = $date;	        		
	        		if ($row['indexType']=='A'){
	        			$reRows[$row['date']]['A-pv'] = $row['pv'];
	        			$reRows[$row['date']]['A-orderNum'] = $row['orderNum'];
	        		}
	        		if ($row['indexType']=='B'){
	        			$reRows[$row['date']]['B-pv'] = $row['pv'];
	        			$reRows[$row['date']]['B-orderNum'] = $row['orderNum'];
	        		}
	        	}
	        }
        }
        $reRows = count($reRows)>0 ? sysSortArray($reRows,'date','SORT_DESC') : array();
        
        $count = array();
        $count['A-pv'] = 0;
        $count['A-orderNum'] = 0;
        $count['B-pv'] = 0;
        $count['B-orderNum'] = 0;
        foreach($reRows as $var=>$row) {
            $count['A-pv'] = $count['A-pv'] + $row['A-pv'];   
            $count['A-orderNum'] = $count['A-orderNum'] + $row['A-orderNum']; 
            $count['B-pv'] = $count['B-pv'] + $row['B-pv']; 
            $count['B-orderNum'] = $count['B-orderNum'] + $row['B-orderNum'];        
        }
        
        $this->view->rows = $reRows;
        $this->view->count = $count;
        $this->view->vars = $vars;
         
        //操作系统
        $osNameRows = $this->_model ->getOsNameArray();
        $this->view->osNameRows = $osNameRows;
        
        //渠道列表
        $memberRows = $this->_model->getMemberArray($this->_productCode,$vars);
        $this->view->memberRows = $memberRows;
        
    }
    
    
    /**
     * 关键词分析
     * 
     * @return void
     */
    public function keywordsAction()
    {

        //获取当前页码
        $pageSize = 20;
        $pageNo = intval($this->_request->get('pageNo'));
        if (empty($pageNo)) {
            $pageNo = 1;
        }
        //设置URL模板以及条件
        $urls = array();
        $vars = array();
        $wheres = array();

        
        $vars['osName'] = trim($this->_request->get('osName'));
        $vars['source'] = trim($this->_request->get('source'));
        $vars['sourceId'] = trim($this->_request->get('sourceId'));
        
        $vars['dateType']  = trim($this->_request->get('dateType'))!='' ? trim($this->_request->get('dateType')) : 1;
        $vars['doType'] = trim($this->_request->get('doType'))!='' ? trim($this->_request->get('doType')) : 'amount';
        $vars['startTime'] = trim($this->_request->get('startTime'));
        $vars['endTime'] = trim($this->_request->get('endTime'));
        $vars['export']   = trim($this->_request->get('export'))!='' ? trim($this->_request->get('export')) :'no';
        
        if ($vars['dateType']==1){
            $vars['startTime'] = date('Y-m-d',strtotime("-1 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==2){
            $vars['startTime'] = date('Y-m-d',strtotime("-8 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==3){
            $vars['startTime'] = date('Y-m-d',strtotime("-31 day"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==4){
            $vars['startTime'] = date('Y-m-d',strtotime("-3 months"));
            $vars['endTime']   = date('Y-m-d',strtotime("-1 day"));
        } elseif ($vars['dateType']==5){
            $vars['startTime'] = '';
            $vars['endTime']   = '';
        }
        
        $urls = $vars;

        $wheres[] = "`productCode` = '{$this->_productCode}'";
        
        $fileNameNote = array();
        if ($vars['startTime'] != '') {
            $wheres[] = "`date` >= '{$vars['startTime']}' ";
            $fileNameNote[] = $vars['startTime'];
        }
        if ($vars['endTime'] != '') {
            $wheres[] = "`date` <= '{$vars['endTime']}' ";
            $fileNameNote[] = $vars['endTime'];
        }
        if ($vars['osName'] != '') {
            $wheres[] = "`osName` = '{$vars['osName']}' ";
            $fileNameNote[] = $vars['osName'];
        }
        if ($vars['source'] != '') {
            $wheres[] = "`sourceId` IN (SELECT  ms.`sourceId` FROM `mob_source` AS ms INNER JOIN `mob_member` AS mm ON  ms.memberCode=mm.code   WHERE   mm.name='{$vars['source']}' AND ms.productCode='{$this->_productCode}') ";
            $fileNameNote[] = $vars['source'];
        }
        if ($vars['sourceId'] != '') {
            $wheres[] = "`sourceId` = '{$vars['sourceId']}' ";
            $fileNameNote[] = $vars['sourceId'];
        }
        
        $wheres[] =" ifnull(`keyword`, '')!=' '";
        
        $wheres[] ="`date`>='2011-10-13'";
        $orderBy = " pv DESC";

        //清理测试数据
        $wheres[] = $this->_whereByClear2;

        $urls['pageNo'] = '{page}';
        $urlTemplate = $this->view->projectUrl($urls);
        
        //获取总记录
        $where = count($wheres) > 0 ? ' WHERE ' . implode(' AND ', $wheres) . ' ' : '';
        $keywordsModel = new Application_Model_ActDayDataKeywords();
        
        $sql = "SELECT keyword,sum(pv) as pv,sum(uv) as uv FROM `{$keywordsModel->getTableName()}` {$where} GROUP BY keyword ORDER BY {$orderBy}";
        $dbPaginator = new Application_Model_DbPaginator($sql, $pageSize, $pageNo);
        $recordCount = $dbPaginator->getRecodCount();
        
        //获取分页html字符
        Zend_Loader::loadClass('Project_Paginator');
        $paginator = new Project_Paginator($recordCount, $pageSize, $pageNo);
        $paginator->urlTemplateContent = $urlTemplate;

        $rows = $dbPaginator->getRows();

        //分配view(分页处理后的记录集以及分页HTML字符)
        $this->view->pageString = $paginator->getPageString();
        $this->view->rows = $rows;
        $urls['pageNo'] = $pageNo;
        $this->view->pageUrl = $this->view->projectUrl($urls);
        $this->view->vars = $vars;
         
        //操作系统
        $osNameRows = $this->_model ->getOsNameArray();
        $this->view->osNameRows = $osNameRows;
        
        //渠道列表
        $memberRows = $this->_model->getMemberArray($this->_productCode,$vars);
        $this->view->memberRows = $memberRows;
        
        
        //导出Excel
        if ($vars['export']=='yes'){
        	$rows = $keywordsModel ->getAdapter()->fetchAll($sql);
            $exportArray = array();
            $exportNum =1;
            foreach ($rows as $row){
                $exportNum++;
                $exportArray['A'.$exportNum]= $row['keyword'];
                $exportArray['B'.$exportNum]= $row['pv'];
                $exportArray['C'.$exportNum]= $row['uv'];
            }
            $table = array();
            $table[0]['tableName'] = 'keyword';
            $table[0]['title'] = array('关键词','搜索次数','搜索人数');
            $table[0]['con'] = $exportArray;
            $fileName = count($fileNameNote) > 0 ? iconv('UTF-8','GBK//IGNORE','搜索关键词分析：'). implode('_', $fileNameNote): iconv('UTF-8','GBK//IGNORE','搜索关键词分析');
            $conversionModel = new Application_Model_ActDayDataConversion();
            $conversionModel->excelExport($table,$fileName);
        }
        
    }
    
    
    
    
    
    
    
}